const db = require("../../lib/db");


//获取文件
exports.getFilePage = (pageSize, nowPage, subsetId) => {
    let _sql;
    let params = [];
    
    if (subsetId > -1 && typeof subsetId =='number') {
      _sql = `SELECT * FROM file WHERE subset_id = ? order by id desc limit ?, ?`;
      params = [subsetId, (nowPage - 1) * pageSize, pageSize];
    } else if (typeof subsetId =='string') {
      // 处理字符串形式的多个ID，需要先分割成数组
      const idArray = subsetId.split(',').map(id => id.trim()).filter(id => id);
      if (idArray.length > 0) {
        const placeholders = idArray.map(() => '?').join(',');
        _sql = `SELECT * FROM file WHERE subset_id is null or subset_id not in (${placeholders}) order by id desc limit ?, ?`;
        params = [...idArray, (nowPage - 1) * pageSize, pageSize];
      } else {
        _sql = `SELECT * FROM file order by id desc limit ?, ?`;
        params = [(nowPage - 1) * pageSize, pageSize];
      }
    } else {
      _sql = `SELECT * FROM file order by id desc limit ?, ?`;
      params = [(nowPage - 1) * pageSize, pageSize];
    }
    return db.queryWithPool(_sql, params);
  }
  
  //获取文件总数
  exports.getFileCount = (subsetId) => {
      let _sql;
      let params = [];
      
    if (subsetId > -1 && typeof subsetId =='number') {
      _sql = `SELECT count(*) AS count FROM file WHERE subset_id = ?`;
      params = [subsetId];
    } else if (typeof subsetId =='string') {
      const idArray = subsetId.split(',').map(id => id.trim()).filter(id => id);
      if (idArray.length > 0) {
        const placeholders = idArray.map(() => '?').join(',');
        _sql = `SELECT count(*) AS count FROM file WHERE subset_id is null or subset_id not in (${placeholders})`;
        params = idArray;
      } else {
        _sql = `SELECT count(*) AS count FROM file`;
        params = [];
      }
    } else {
      _sql = `SELECT count(*) AS count FROM file`;
      params = [];
    }
    return db.queryWithPool(_sql, params);
  }
  
  // 移动文件
  exports.removeFile = (id, subsetId) => {
    let _sql = `UPDATE file SET subset_id = ? WHERE id = ?`;
    return db.queryWithPool(_sql, [subsetId, id]);
  }
  
  //(上传)新建文件
exports.uploadFile = (value) => {
    let _sql = `INSERT INTO file set ?`;
    return db.queryWithPool(_sql, value);
  }
  
  //通过ID查询文件
  exports.getFileById = (id) => {
    let _sql = `SELECT * FROM file WHERE id = ?`;
    return db.queryWithPool(_sql, [id]);
  }

  //删除文件,根据文件ID
  exports.deleteFile = (id) => {
    let _sql;
    let params = [];
    
    if (typeof id == 'number') {
      _sql = `DELETE FROM file WHERE id = ?`;
      params = [id];
    } else {
      // 处理数组形式的多个ID
      const idArray = String(id).split(',').map(id => id.trim()).filter(id => id);
      if (idArray.length > 0) {
        const placeholders = idArray.map(() => '?').join(',');
        _sql = `DELETE FROM file WHERE id in (${placeholders})`;
        params = idArray;
      } else {
        _sql = `DELETE FROM file WHERE id = ?`;
        params = [id];
      }
    }
    return db.queryWithPool(_sql, params);
  }

  //根据文件路径删除文件
  exports.deleteFileByPath = (fileUrl) => {
    let _sql;
    let params = [];
    
    if (typeof fileUrl == 'string') {
      _sql = `DELETE FROM file WHERE url = ?`;
      params = [fileUrl];
    } else {
      // 处理数组形式的多个URL
      const urlArray = String(fileUrl).split(',').map(url => url.trim()).filter(url => url);
      if (urlArray.length > 0) {
        const placeholders = urlArray.map(() => '?').join(',');
        _sql = `DELETE FROM file WHERE url in (${placeholders})`;
        params = urlArray;
      } else {
        _sql = `DELETE FROM file WHERE url = ?`;
        params = [fileUrl];
      }
    }
    return db.queryWithPool(_sql, params);
  }
